# 定义数据库操作
from sqlalchemy import and_
from app.models import *
from app import db
from datetime import datetime


# User
# 增
def create_user(UserName, UserPassword):
    user = User()
    user.UserName = UserName
    user.set_password(UserPassword)
    db.session.add(user)
    # 将数据库会话中的变动提交到数据库中, 记住, 如果不 commit, 数据库中是没有变化的.
    db.session.commit()


# 删
def delete_user(UserID):
    user = User.query.filter_by(UserID=UserID).first()
    db.session.delete(user)
    db.session.commit()


# 改
def update_user(UserID, new_UserName=None, new_UserPassword=None, new_UserAddress=None, new_UserMoney=None):
    # 获取用户对象
    user = User.query.filter_by(UserID=UserID).first()
    if user is None:
        return "user is None"
    # 修改用户
    if new_UserName is not None:
        user.UserName = new_UserName
    if new_UserPassword is not None:
        user.set_password(new_UserPassword)
    if new_UserAddress is not None:
        user.UserAddress = new_UserAddress
    if new_UserMoney is not None:
        user.UserMoney = new_UserMoney
    db.session.commit()


def increase_UserMoney_by_UserID_And_Money_Increment(UserID, Increment=None):
    user = User.query.filter_by(UserID=UserID).first()
    if Increment is not None:
        user.UserMoney += Increment


def decrease_UserMoney_by_UserID_And_Money_Decrement(UserID, Decrement=None):
    user = User.query.filter_by(UserID=UserID).first()
    if Decrement is not None:
        user.UserMoney -= Decrement
    if user.UserMoney < 0:
        print("user.UserMoney < 0")


# 修改用户头像路径
def update_userAvatarPath(UserID, new_UserAvatarPath):
    # 更新方式: update子查询   可以避免更新丢失问题
    User.query.filter(UserID == UserID).update({'UserAvatarPath': new_UserAvatarPath})
    # 提交会话
    db.session.commit()


# 查
def query_user_by_UserID(UserID):
    return User.query.filter_by(UserID=UserID).first()


def query_user_by_UserName(UserName):
    return User.query.filter_by(UserName=UserName).first()


# 查用户的头像
def query_UserAvatarPath_by_UserID(UserID):
    return User.query.filter_by(UserID=UserID).first()['UserAvatarPath']


################################################################################
# GoodsType
# 增
def create_goods_type(GoodsTypeName):
    goods_type = GoodsType()
    goods_type.GoodsTypeName = GoodsTypeName
    db.session.add(goods_type)
    db.session.commit()


# 删
def delete_goods_types(GoodsTypeName):
    goods_types = GoodsType.query.filter_by(GoodsTypeName=GoodsTypeName).first()
    db.session.delete(goods_types)
    db.session.commit()


# 查
def query_all_goods_types():
    all_goods_types = GoodsType.query.all()
    return all_goods_types


################################################################################
# GoodsSubType
# 增
def create_goods_sub_type(GoodsTypeName, GoodsSubTypeName):
    goods_sub_type = GoodsSubType()
    goods_sub_type.GoodsTypeName = GoodsTypeName
    goods_sub_type.GoodsSubTypeName = GoodsSubTypeName
    db.session.add(goods_sub_type)
    db.session.commit()


# 删
def delete_goods_sub_type(GoodsSubTypeName):
    goods_sub_type = GoodsType.query.filter_by(GoodsSubTypeName=GoodsSubTypeName).first()
    db.session.delete(goods_sub_type)
    db.session.commit()


# 查
def query_goods_sub_type_by_goods_sub_type_name(GoodsSubTypeName):
    return GoodsSubType.query.filter_by(GoodsSubTypeName=GoodsSubTypeName).first()

def query_all_goods_sub_type_by_goods_types_name(GoodsTypeName):
    return GoodsSubType.query.filter_by(GoodsTypeName=GoodsTypeName).all()


################################################################################
# Goods
# 增
def create_goods(GoodsSubTypeName, GoodsName, GoodsPrice, GoodsCount,
                 GoodsWeight=None, GoodsIntroduction=None, GoodsImage=None):
    # 创建一个新用户对象
    goods = Goods()
    goods.GoodsSubTypeName = GoodsSubTypeName
    goods.GoodsName = GoodsName
    goods.GoodsCount = GoodsCount
    goods.GoodsPrice = GoodsPrice
    if GoodsWeight is not None:
        goods.GoodsWeight = GoodsWeight
    if GoodsIntroduction is not None:
        goods.GoodsIntroduction = GoodsIntroduction
    if GoodsImage is not None:
        goods.GoodsImage = GoodsImage
    # 将新创建的用户添加到数据库会话中
    db.session.add(goods)
    # 将数据库会话中的变动提交到数据库中, 记住, 如果不 commit, 数据库中是没有变化的.
    db.session.commit()


# 删
def delete_goods(GoodsID):
    goods = User.query.filter_by(GoodsID=GoodsID).first()
    db.session.delete(goods)
    db.session.commit()


# 改
def decrease_GoodsCount_And_increase_GoodsSell_by_GoodsID_And_BuyNumber(GoodsID, BuyNumber):
    # 获取用户对象
    goods = Goods.query.filter_by(GoodsID=GoodsID).first()
    if goods is None:
        print("goods is None")
        return "goods is None"
    goods.GoodsCount -= BuyNumber
    if goods.GoodsCount < 0:
        print("goods.GoodsCount < 0")
        return "goods.GoodsCount < 0"
    goods.GoodsSell += BuyNumber
    db.session.commit()


# 查
def query_goods_by_GoodsID(GoodsID):
    goods = Goods.query.filter_by(GoodsID=GoodsID).first()
    return goods


def query_goods_by_GoodsName(GoodsTypeName):
    type_goods = Goods.query.filter_by(GoodsTypeName=GoodsTypeName).all()
    Type_goods = []
    for item in type_goods:
        Type_goods.append(item.GoodsName)
    return Type_goods


def query_all_goods_by_goods_sub_type_name(GoodsSubTypeName):
    return Goods.query.filter_by(GoodsSubTypeName=GoodsSubTypeName).all()


#########################################################################
# 订单增删改查
# Order
# 增
def create_order_return_OrderID(UserID):
    # 创建一个新用户对象
    order = Order()
    order.UserID = UserID
    order.CreateTime = datetime.now()
    db.session.add(order)
    db.session.commit()
    return order.OrderID


# 改
def add_Order_TotalPayment_by_OrderID_And_ItemPayment(OrderID, ItemPayment):
    order = Order.query.filter_by(OrderID=OrderID).first()
    order.TotalPayment += float(ItemPayment)
    db.session.commit()


# 删
def delete_order(OrderID):
    order = Order.query.filter_by(OrderID=OrderID).first()
    db.session.delete(order)
    db.session.commit()


# 查
def query_order_by_OrderID(OrderID):
    return Order.query.filter_by(OrderID=OrderID).first()

def query_all_order_by_UserID(UserID):
    return Order.query.filter_by(UserID=UserID).all()


#########################################################################
# 订单详情增删改查
# OrderItem
# 增
def create_order_item(OrderID, GoodsID, BuyNumber):
    # 创建一个新用户对象
    order_item = OrderItem()
    print("创建order_item")
    print(OrderID)
    order_item.OrderID = OrderID
    order_item.GoodsID = GoodsID
    goods = Goods.query.filter_by(GoodsID=GoodsID).first()
    order_item.GoodsName = goods.GoodsName
    order_item.GoodsPrice = goods.GoodsPrice
    order_item.BuyNumber = int(BuyNumber)
    order_item.ItemPayment = int(BuyNumber) * float(goods.GoodsPrice)
    add_Order_TotalPayment_by_OrderID_And_ItemPayment(OrderID, order_item.ItemPayment)
    db.session.add(order_item)
    db.session.commit()


# 删
def delete_OrderItems(OrderID):
    order_items = OrderItem.query.filter_by(OrderID=OrderID).all()
    # 删除用户
    db.session.delete(order_items)
    # 提交数据库会话
    db.session.commit()


# 查
def query_all_order_item_by_OrderID(OrderID):
    return OrderItem.query.filter_by(OrderID=OrderID).all()


#########################################################################
# 购物车增删改查
# BuyCar
# 增
def create_BuyCar(UserID, GoodsID, GoodsName, BuyCarNumber, GoodsPrice, TotalPrice):
    # 创建一个新用户对象
    buycar = BuyCar()
    buycar.UserID = UserID
    buycar.GoodsID = GoodsID
    buycar.GoodsName = GoodsName
    buycar.BuyCarNumber = BuyCarNumber
    buycar.GoodsPrice = GoodsPrice
    buycar.TotalPrice = TotalPrice
    db.session.add(buycar)
    db.session.commit()


# 改
def add_BuyCarNumber_By_BuyCarID_And_BuyCarNumber(BuyCarID, BuyCarNumber):
    buycar = BuyCar.query.filter_by(BuyCarID=BuyCarID).first()
    buycar.BuyCarNumber += int(BuyCarNumber)
    db.session.commit()


# 删
def delete_BuyCar_by_BuyCarID(BuyCarID):
    buycar = BuyCar.query.filter_by(BuyCarID=BuyCarID).first()
    db.session.delete(buycar)
    db.session.commit()


def delete_all_BuyCar_by_UserID(UserID):
    buycar = BuyCar.query.filter_by(UserID=UserID).all()
    db.session.delete(buycar)
    db.session.commit()




# 查
def query_BuyCar_by_BuyCarID(BuyCarID):
    return BuyCar.query.filter_by(BuyCarID=BuyCarID).first()


def query_BuyCar_by_UserID_And_GoodsID(UserID, GoodsID):
    return BuyCar.query.filter(and_(BuyCar.UserID == UserID, BuyCar.GoodsID == GoodsID)).first()


def query_all_BuyCar_by_UserID(UserID):
    return BuyCar.query.filter_by(UserID=UserID).all()


# 后台管理员创建测试数据
def create_test_statics():
    # 分类名创建分类
    create_goods_type("水果")
    create_goods_type("禽肉蛋类")
    create_goods_type("水产海鲜")
    create_goods_type("蔬菜")
    create_goods_type("花卉")
    create_goods_type("农副产品")
    create_goods_type("药草")

    # 指定分类名下创建子类名
    create_goods_sub_type("水果", "荔枝")
    create_goods_sub_type("水果", "杨梅")
    create_goods_sub_type("水果", "猕猴桃")
    create_goods_sub_type("水果", "桃子")
    create_goods_sub_type("水果", "柚子")
    create_goods_sub_type("水果", "橙子")
    create_goods_sub_type("水果", "芒果")
    create_goods_sub_type("水果", "龙眼")
    create_goods_sub_type("水果", "西瓜")
    create_goods_sub_type("水果", "柑橘")
    create_goods_sub_type("水果", "梨")
    create_goods_sub_type("蔬菜", "菜心")
    create_goods_sub_type("蔬菜", "佛手瓜")
    create_goods_sub_type("水产海鲜", "鱼")
    create_goods_sub_type("水产海鲜", "虾")
    create_goods_sub_type("禽肉蛋类", "鹅")
    create_goods_sub_type("禽肉蛋类", "鸡")
    create_goods_sub_type("农副产品", "皮蛋")
    create_goods_sub_type("农副产品", "米")
    create_goods_sub_type("农副产品", "茶")
    create_goods_sub_type("农副产品", "油")
    create_goods_sub_type("药草", "良姜")
    create_goods_sub_type("药草", "砂仁")
    create_goods_sub_type("花卉", "兰花")
    # 指定子类名创建商品名、价格等属性#价格数量重量简介图片
    create_goods("荔枝", "增城荔枝", 10, 200, None, None, None)
    create_goods("荔枝", "从化荔枝", 10, 200, None, None, None)
    create_goods("荔枝", "镇隆荔枝", 9, 200, None, None, None)
    create_goods("荔枝", "高州荔枝", 11, 200, None, None, None)
    create_goods("杨梅", "始兴杨梅", 12, 200, None, None, None)
    create_goods("猕猴桃", "和平猕猴桃", 15, 200, None, None, None)
    create_goods("桃子", "连平鹰嘴蜜桃", 13, 300, None, None, None)
    create_goods("柚子", "梅县金柚", 4.5, 400, None, None, None)
    create_goods("柚子", "大埔蜜柚", 5, 400, None, None, None)
    create_goods("橙子", "平远脐橙", 8, 400, None, None, None)
    create_goods("橙子", "廉江红橙", 9, 400, None, None, None)
    create_goods("芒果", "覃斗芒果", 11, 300, None, None, None)
    create_goods("龙眼", "高州龙眼", 13, 300, None, None, None)
    create_goods("西瓜", "谭脉西瓜", 4.5, 500, None, None, None)
    create_goods("柑橘", "仁化贡柑", 4, 400, None, None, None)
    create_goods("柑橘", "化州橘红", 12, 100, None, None, None)
    create_goods("柑橘", "德庆贡柑", 4.5, 100, None, None, None)
    create_goods("梨", "连州水晶梨", 5.5, 300, None, None, None)
    create_goods("菜心", "增城迟菜心", 4, 200, None, None, None)
    create_goods("菜心", "连州菜心", 4.5, 200, None, None, None)
    create_goods("佛手瓜", "新丰佛手瓜", 4.5, 200, None, None, None)
    create_goods("鱼", "白蕉海鲈", 40, 100, None, None, None)
    create_goods("鱼", "中山脆肉鲩", 35, 100, None, None, None)
    create_goods("鱼", "茂名罗非鱼", 9.9, 100, None, None, None)
    create_goods("虾", "电白南美白对虾", 35, 100, None, None, None)
    create_goods("鹅", "马冈鹅", 29, 100, None, None, None)
    create_goods("鸡", "信宜怀乡鸡", 25, 100, None, None, None)
    create_goods("鸡", "封开杏花鸡", 30, 100, None, None, None)
    create_goods("皮蛋", "开平皮蛋", 22, 100, None, None, None)
    create_goods("米", "连山大米", 5, 500, None, None, None)
    create_goods("米", "台山大米", 5, 500, None, None, None)
    create_goods("茶", "沿溪山白毛尖", 400, 100, None, None, None)
    create_goods("茶", "罗坑茶", 150, 100, None, None, None)
    create_goods("茶", "紫金绿茶", 200, 100, None, None, None)
    create_goods("茶", "新会柑茶", 300, 100, None, None, None)
    create_goods("茶", "鹤山红茶", 300, 100, None, None, None)
    create_goods("茶", "英德红茶", 200, 100, None, None, None)
    create_goods("油", "龙川山茶油", 99, 100, None, None, None)
    create_goods("良姜", "徐闻良姜", 25, 100, None, None, None)
    create_goods("砂仁", "阳春春砂仁", 29.9, 100, None, None, None)
    create_goods("兰花", "翁源兰花", 68, 100, None, None, None)
    # 分类名创建分类
    # create_goods_type("水果")
    # create_goods_type("肉类")
    # create_goods_type("蔬菜")
    # # 指定分类名下创建子类名
    # create_goods_sub_type("水果", "苹果")
    # create_goods_sub_type("水果", "香梨")
    # create_goods_sub_type("水果", "橙子")
    # create_goods_sub_type("水果", "香蕉")
    # create_goods_sub_type("肉类", "猪肉")
    # create_goods_sub_type("肉类", "鸡肉")
    # create_goods_sub_type("肉类", "牛肉")
    # create_goods_sub_type("肉类", "羊肉")
    # create_goods_sub_type("蔬菜", "白菜")
    # create_goods_sub_type("蔬菜", "菠菜")
    # create_goods_sub_type("蔬菜", "空心菜")
    # create_goods_sub_type("蔬菜", "西蓝花")
    # # 指定子类名创建商品名、价格等属性
    # create_goods("苹果", "苹果A", 4, 100, None, None, None)
    # create_goods("苹果", "苹果B", 5, 20, None, None, None)
    # create_goods("苹果", "苹果C", 4, 35, None, None, None)
    # create_goods("牛肉", "神户牛肉", 90, 100, None, None, None)
    # create_goods("牛肉", "澳洲牛肉", 50, 100, None, None, None)
    # create_goods("牛肉", "美国牛肉", 60, 100, None, None, None)
